IF OBJECT_ID('dbo.RptAgreementDeliverables') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.RptAgreementDeliverables
    IF OBJECT_ID('dbo.RptAgreementDeliverables') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.RptAgreementDeliverables >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.RptAgreementDeliverables >>>'
END
go
-- =======================================================================
-- Procedure Name: dbo.RptAgreementDeliverables
--
-- Author:      Kevin Cashman, Parallax Consulting LLC
--
-- Create date: 5/31/2007
--
-- Description: Source of Report, "AgreementDeliverables.rdl"
--              In Insight DB Reporting Services
--
-- Parameters:  
--          @StartDate                  
--          @EndDate                    
--          @Institution                
--          @ProposalNumber             
--          @FundNumber                 
--          @DeliverableCategory        
--          @PrincipalInvestigator      
--          @DeliverableStatus          
--          @Department                 
--          @domainUserId               
--              
-- Returns:     INT, 0=Success, -1=Error
--
-- SampleCall:
-- EXECUTE dbo.RptAgreementDeliverables()
--
--------------------------------------------------------------------------
-- Date        Initials  Modification
--------------------------------------------------------------------------
-- 6/8/07      KC        Created
-- 7/12/07     KC        Changed Naming Conventions 
-- 7/20/07     KC        Finalized, sent to QA.
-- 7/23/07     KC        Changed Milestone source for DueDate to 
--                       dbo.InfoEdPTDeliverable
-- 8/2/07      KC        Addressed QA Feedback, BudgetDate fix.
-- 8/6/2007    KC        Added '0' for 'ALL' in Params
-- 8/8/07      KC        Made Security Inner Join
-- 8/17/07     KC        Security changed to Cached table
-- 8/24/07     KC        Finalized for QA. Filtered out CSCR Categories
--                       and removed @DeliverableCategory
-- 8/27/07     KC        Put back DeliverableCategory, misunderstanding
--                       Cleaned up checks for ProposalNumber and FundNumber
--                       Filtered CSCR AGAIN!!!
-- 9/10/07     KC        Changed to new source tables
-- 9/19/07     KC        Added PreAwardMgr to result set.
-- 9/25/07     KC        Used ID for Institution, Dept
-- 9/26/07     KC        Added @Unit
-- 9/28/07     KC        Added @PreAwardManager & @PostAwardManager
-- 10/1/07     KC        Fixed outdated function reference. fnGetMultiCriteria
-- 10/10/07    KC        Fix for ProjectFundNumber
-- 12/17/08    KC        New Security function
-- 01/05/08    KC        Security Function Perf Issues: use TempTable
-- 08/12/09	   SP	     Removed the filter for the CSCR cases
--
-- =======================================================================
CREATE PROCEDURE dbo.RptAgreementDeliverables
     (
          @StartDate                  DATETIME,
          @EndDate                    DATETIME,
          @Institution                VARCHAR(100) = NULL,
          @Department                 VARCHAR(100) = NULL,
          @Unit                       VARCHAR(100) = NULL,
          @PreAwardManager            VARCHAR(100) = NULL,
          @PostAwardManager           VARCHAR(100) = NULL,
          @ProposalNumber             VARCHAR(20) = NULL,        -- All or One, not Multi
          @FundNumber                 VARCHAR(10) = NULL,
          @DeliverableCategory        VARCHAR(200) = NULL,
          @PrincipalInvestigator      VARCHAR(100) = NULL,
          @DeliverableStatus          VARCHAR(200) = NULL,
          @DomainUserId               INT
     )
AS
BEGIN
     -- Initial Settings
     SET NOCOUNT ON
     
     -- Declare Local Variables
     DECLARE   @Error            INT,
               @RowCount         INT

     -- ALL checking
     IF @Institution IN ('ALL','','0') SET @Institution = NULL
     IF @Department IN ('ALL','','0') SET @Department = NULL
     IF @Unit IN ('ALL','','0') SET @Unit = NULL
     IF @PreAwardManager IN ('ALL','','0') SET @PreAwardManager = NULL
     IF @PostAwardManager IN ('ALL','','0') SET @PostAwardManager = NULL
     IF @DeliverableCategory IN ('ALL','','0') SET @DeliverableCategory = NULL
     IF @DeliverableStatus IN ('ALL','','0') SET @DeliverableStatus = NULL
     IF @PrincipalInvestigator IN ('ALL','','0') SET @PrincipalInvestigator = NULL
     IF @ProposalNumber IN ('ALL','','0') SET @ProposalNumber = NULL
     IF @FundNumber IN ('ALL','','0') SET @FundNumber = NULL

     -- Performance Issues, Get Secured AgreementIds into a temp table
     CREATE TABLE   #tt_Agreements  (AgreementId INT)
     
     INSERT    #tt_Agreements (AgreementId)
     SELECT    AgreementId
     FROM      dbo.fnGetSecurityAgreements(@DomainUserId) 

     -- Return ResultSet (Need Distinct on ResultSet because vwInfoEdDeliverables could have multiple statuses
     SELECT    DISTINCT ISNULL(BUDG.FundNumber,AI.ProjectFundNumber) AS FundNumber,  
               AI.FolderNumber AS ProposalNumber,        
               AI.PrincipalInvestigator AS PrincipalInvestigator,
               UDFDel.REPORTCAT AS DeliverableCategory,
               AI.Department AS Department,
               AI.ChiefCode AS ChiefCode,
               AI.Unit AS Unit,
               AI.SponsorName AS Sponsor,        
               AI.ProposalTitle AS ProposalTitle,     
               CONVERT(VARCHAR(12),UDFDel.DueDate,101) AS DueDate,
               AI.Institution AS Institution,
               AI.PostAwardManagerCode AS PostAwardManager,
               ISNULL(AI.PostAwardManagerName,'Post Award Manager Not Assigned') AS PostAwardManagerName,
               AI.PreAwardManager AS PreAwardManager,
               ISNULL(AI.PreAwardManagerName,'Pre Award Manager Not Assigned') AS PreAwardManagerName,
               
               -- Addt'l Informational Fields
               UDFDel.ReportID AS DeliverableReportID,
               AI.InfoEdPropNumber AS InfoEdPropNumber,
               AI.PrincipalInvestigatorId AS PrincipalInvestigatorId,
               AI.ResourceId AS AgrResourceId,
               AI.AgreementId AS AgreementId
     FROM      dbo.AgrAgreement_Info AI LEFT OUTER JOIN 
               
               -- We need only the latest BudgetYear and BudgetDates for each 
               -- FolderNumber within the search date params.
               dbo.fnGetPeriodBudget(@StartDate, @EndDate) BUDG ON AI.AgreementId = BUDG.AgreementId INNER JOIN
               
               -- Security Issues: Not all Users can see all Agreements!
               #tt_Agreements AUTH ON AI.AgreementId = AUTH.AgreementId INNER JOIN
                         
               dbo.vwInfoEdDeliverables UDFDel ON AI.InfoEdPropNumber = UDFDel.prop_no 
               
     WHERE     (@StartDate IS NULL OR UDFDel.DueDate BETWEEN @StartDate AND @EndDate) AND
               (@Institution IS NULL OR AI.InstitutionId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria(@Institution))) AND
               (@Department IS NULL OR AI.DepartmentId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria(@Department))) AND
               (@Unit IS NULL OR AI.UnitId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria(@Unit))) AND
               (@PreAwardManager IS NULL OR AI.PreAwardManagerId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria( @PreAwardManager ))) AND
               (@PostAwardManager IS NULL OR AI.PostAwardManagerId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria( @PostAwardManager ))) AND
               (@ProposalNumber IS NULL OR AI.FolderNumber = @ProposalNumber) AND
               (@FundNumber IS NULL OR BUDG.FundNumber LIKE @FundNumber ) AND
               (@PrincipalInvestigator IS NULL OR AI.PrincipalInvestigatorId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria( @PrincipalInvestigator ))) AND
               (@DeliverableStatus IS NULL OR UDFDel.REPORTSTAT IN (SELECT Code FROM dbo.fnGetMultiCriteriaCode( @DeliverableStatus ))) AND
               (@DeliverableCategory IS NULL OR UDFDel.REPORTCAT IN (SELECT Code FROM dbo.fnGetMultiCriteriaCode( @DeliverableCategory ))) --AND	-- by Surajit on 08/12/2009
               
               -- Filter these out
               --(UDFDel.REPORTCAT NOT LIKE 'CSCR%') AND	-- by Surajit on 08/12/2009
               --(UDFDel.REPORTSTAT NOT LIKE 'CSCR%')		-- by Surajit on 08/12/2009
     ORDER BY UDFDel.DueDate ASC

     SELECT @Error=@@ERROR,@RowCount=@@ROWCOUNT
     
     DROP TABLE #tt_Agreements
     
     IF @Error!=0
          RETURN -1
          
     -- Return Success
     RETURN 0
     
END
go

IF OBJECT_ID('dbo.RptAgreementDeliverables') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE dbo.RptAgreementDeliverables >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.RptAgreementDeliverables >>>'
go

GRANT EXECUTE ON dbo.RptAgreementDeliverables TO InsightReport,Insight
go
